#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my %result;
my %infos;

foreach($workbook->worksheets()){
	my $microcebe;
	$worksheet=$_;
	
	### recuperation code_animal ###
    my $micro=$worksheet->get_name();
    if ($micro ne '6TST-fichier type'){
		my @mic=split('#',$micro);
		$micro=$mic[1];
		@mic=split(' ',$micro);
		$microcebe=$mic[0];
		my $size=@mic;
		my $valeur;
		
		if($size>1){$valeur=$mic[1];}
		else{$valeur=0;}
		
		my ($row_min,$row_max) = $worksheet->row_range();
		my ($col_min, $col_max) = $worksheet->col_range();
		
		if($valeur ne 'hand'){ # si ce n'est pas la feuille avec hand projection
			for my $row (3 .. $row_max){ #parcours lignes
				my $date_test='';
				my  @test;
				
				for my $col ($col_min .. $col_max){ #parcours colonnes
					if ($col eq 0){
						my $cont=$worksheet->get_cell($row,$col);
						my $cont2=$worksheet->get_cell($row+1,$col);
						if($cont2){
							my $date1=$cont->value();
							my $date2=$cont2->value();
							if($date1 ne $date2){ $date_test=$date1; }
						}
						else{
							if($cont){ $date_test=$cont->value(); }
						}
					}
					
					if($date_test ne ''){
						if($col>=10 and $col<=16){ #recup temps par tube + nbtrials
							my $cont=$worksheet->get_cell($row,$col);
							push(@test, $cont->value());
						}
						#composition de @test: T1,T2,T3,T4,T5,T6,nbtrials
					}
				}
				if($date_test ne ''){ $infos{$date_test}=\@test; }
			}
		}
		if ($valeur eq 'post-injection'){ 
			$result{$microcebe}={%infos};
			%infos=();
		}
	}
}
		
#~ my $util;
#~ foreach $util (keys %result) {
	#~ print "\n\n$util : \n";
	#~ my $cpt=0;
    #~ foreach (keys %{$result{$util}}) {
		#~ print "$_ -> $result{$util}{$_}[0], $result{$util}{$_}[1], $result{$util}{$_}[2], $result{$util}{$_}[3], $result{$util}{$_}[4], $result{$util}{$_}[5], $result{$util}{$_}[6]\n";
		#~ $cpt++;
    #~ }
    #~ print "$cpt";
#~ }



# ********************* INSERTION DONNEES DANS LA BASE **************************** #

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
# execute INSERT query

# ************************************* Création type_test ************************************* #

my @unite_mesure=('T1','T2','T3','T4','T5','T6','Nombre essais');
my @id_type_test;
my $row=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='6tst' AND unite_mesure=?");
foreach my $elt(@unite_mesure){
	$row->execute($elt);
	while(my @unite=$row->fetchrow_array()){
		push(@id_type_test,$unite[0]);
	}
	$row->finish();
}
if(not @id_type_test){
	foreach my $elt(@unite_mesure){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', '6tst', '$elt')"); 
		$row->execute($elt);
		while(my @unite=$row->fetchrow_array()){
			push(@id_type_test,$unite[0]);
		}
		$row->finish();
	}
}

foreach my $code (keys %result) { #$code =code_animal
	#recuperation du code_animal et de sa date de naissance =>creation de l'animal
	my $id_anim;
	
	# ************************************* Création animal ************************************* #
	
	### verification de l'existence de l'animal dans la base
	$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code')");
		
	if (not $id_anim){ # si l'animal n'existait pas
		$dbh->do("INSERT INTO Animal(code_animal) VALUES ($code)");
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code')");
	}
	
	
	foreach (keys %{$result{$code}}) {
		my $datet=$_; #date du test
		my @result2;
		my @id_test2;
		my @id_res2;
		for my $i(0 .. 6){
			$result2[$i]=$result{$code}{$_}[$i];
		}
		
		# ************************************* Création test ************************************* #
		
		### vérification que le test n'existe pas déjà ###
		my $prep_test=$dbh->prepare("SELECT id_test FROM Test WHERE experimentateur='$exp' AND id_type_test=? AND date_test=(E'$datet')");
		foreach my $elt(@id_type_test){
			$prep_test->execute($elt);
			while (my @val=$prep_test->fetchrow_array()){
				push(@id_test2,$val[0]);
			}
			$prep_test->finish();
		}
		
		### si le test n'existe pas on l'ajoute dans TEST ###
		if(not @id_test2){
			foreach my $elt(@id_type_test){
				my $row2=$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$datet', '$exp', '$elt')");
				$prep_test->execute($elt);
				while (my @val=$prep_test->fetchrow_array()){
					push(@id_test2,$val[0]);
				}
				$prep_test->finish();
			}
		}
		
		# ************************************* Création resultat_test ************************************* #
		
		### vérification que resultat_test n'existe pas ###
		my $prep_rtest=$dbh->prepare("SELECT id_res from resultat_test WHERE id_test=? and id_animal=$id_anim");
		foreach my $i(@id_test2){
			$prep_rtest->execute($i);
			while (my @val=$prep_rtest->fetchrow_array()){
				push(@id_res2,$val[0]);
			}
			$prep_rtest->finish();
		}
		
		if(not @id_res2){
			foreach my $l(@id_test2){
				$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($l, $id_anim)");
				$prep_rtest->execute($l);
				while (my @val=$prep_rtest->fetchrow_array()){
					push(@id_res2,$val[0]);
				}
				$prep_rtest->finish();
			}
		}
		
		# ************************************* Création resultat_test_simple ************************************* #

		my $prep_resultat=$dbh->prepare("SELECT id_res FROM resultat_test_simple WHERE mesure=? AND id_res=?");

		for(my $elt=0; $elt<=$#result2; $elt++){
			for(my $elt2=0; $elt2<=$#id_res2; $elt2++){
				if( $elt eq $elt2){
					$prep_resultat->execute($result2[$elt],$id_res2[$elt2]);
					my @val=$prep_resultat->fetchrow_array();
					my $size3=@val;
					$prep_test->finish();
					if($size3 eq 0){
						$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2[$elt2], $result2[$elt])");					
					}
				}
			}
		}
	}
}

$dbh->disconnect();
		
